from openpyxl import load_workbook, Workbook
import datetime
from decimal import *


excel_file = 'D:/Users/zarra/Documents/ownCloud/Documents/总部支付平台/私有化支付/1-绩效考核.xlsx'

xls_columns = ["关联模块","需求","子需求","任务","X1","负责人","PV","计划开始日期","计划完成日期","AC","实际开始日期","实际完成日期","X2","EV","A","备注"]

def main():
    wb = load_workbook(filename=excel_file, data_only=True)
    sheet = wb['统一支付综合管理项目']
    size = sheet.max_row
    index = 2
    columns_count = len(xls_columns)
    sql_columns = ",".join(xls_columns)
    while index < size:

        values = []
        for column_index in range(1, columns_count+1):
            title = xls_columns[column_index-1]
            cell = sheet.cell(row=index, column=column_index)
            value = cell.value
            if value is None:
                value = 'null'
            elif '日期' in title:
                base_date = cell.base_date
                value = base_date + datetime.timedelta(days=value)
                value = "'"+value.strftime('%Y-%m-%d 00:00:00')+"'"
            elif isinstance(value, float):
                value = Decimal(value).quantize(Decimal("0.00"))
                value = str(value)
            elif isinstance(value, int):
                value = str(value)
            elif isinstance(value, str):
                if '#DIV/0' in value:
                    value = 'null'
                else:
                    value = "'"+value+"'"

            values.append(value)


        index += 1
        sql_values = ",".join(values)
        sql = f'insert into jijiao ({sql_columns}) values ({sql_values});'

        print(sql)


if __name__ == '__main__':
    main()